package com.hnkywl.sts.dao.xsgl;

import com.hnkywl.sts.entity.xsgl.Kc;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-4-25
 * Time: 下午2:02
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class KcDao extends SimpleHibernateTemplate<Kc,Long>{
//    public Page<Map> findKcList(Long nbgsId,Date bjrq,int pageNo,int pageSize){
//        Page<Map> page = new Page<Map>(pageNo,pageSize,0);
//        String bjQuery = "(select kcbjlist5_.bj from  T_BIZ_KCBJ kcbjlist5_ where this_.id =" +
//                " kcbjlist5_.KC_ID and kcbjlist5_.bjrq = :bjrq) as y0_," +
//                " (select kcbjlist5_.scbj  from  T_BIZ_KCBJ kcbjlist5_ where this_.id =" +
//                "            kcbjlist5_.KC_ID and kcbjlist5_.bjrq = :bjrq) as y1_," +
//                " (select kcbjlist5_.wj from  T_BIZ_KCBJ kcbjlist5_ where this_.id =" +
//                "            kcbjlist5_.KC_ID and kcbjlist5_.bjrq = :bjrq) as y2_, " ;
//        String query = "  from (select " + (bjrq == null ? "" : bjQuery) +
//                " this_.id  as y3_, this_.kcsl as y4_, gg1_.name as y5_, gg1_.id as y6_, gg1_.cz as y7_, gg1_.ggxh as y8_, cd4_.id as y9_, createuser2_.xm as y10_, cd4_.z as y11_,gg1_.dw as y12_" +
//                "          from T_BIZ_KC this_" +
//                "          left outer join T_SYS_YG createuser2_ on this_.CREATEUSER_ID = createuser2_.id" +
//                "          left outer join T_SYS_GSJG bjgs3_ on this_.BJGS_ID = bjgs3_.id" +
//                "          left outer join T_SYS_SJZDZ cd4_ on this_.CD_SJZDZ_ID = cd4_.id" +
//                "          left outer join T_SYS_GG gg1_ on this_.GG_ID = gg1_.id ";
//        String where = "where (this_.deleted != '1') " + (nbgsId == null ? "" : "and bjgs3_.id = :bjgsId ") + "order by this_.last_updated desc)";
//        String rowNum = " where rownum >= :rowStart and rownum <= :rowEnd";
//        String countQuery = "select count(1) " + query + where;
//        SQLQuery countSQLQuery = getSession().createSQLQuery(countQuery);
//        SQLQuery sqlQuery = getSession().createSQLQuery("select * " + query + where + rowNum);
//        if(bjrq != null) {
//            countSQLQuery.setParameter("bjrq",bjrq);
//            sqlQuery.setParameter("bjrq",bjrq);
//        }
//        if(nbgsId != null) {
//            countSQLQuery.setParameter("bjgsId",nbgsId);
//            sqlQuery.setParameter("bjgsId",nbgsId);
//        }
//        sqlQuery.setParameter("rowStart",(pageNo-1)*pageSize+1);
//        sqlQuery.setParameter("rowEnd",pageNo*pageSize);
//        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
//        page.setTotalCount(((BigDecimal)countSQLQuery.uniqueResult()).intValue());
//        page.setResult(sqlQuery.list());
//        return page;
//    }
    public List<Map> findKcList(Long nbgsId,Date bjrq){
       String query = "select distinct gg.id \"gg.id\",gg.name \"gg.name\",gg.code \"gg.code\",gg.cz \"gg.cz\"," +
               "       gg.ggxh \"gg.ggxh\",gg.dw \"gg.dw\",sjzdz.id \"cd.id\",kcbj.hdbj \"hdbj\"," +
               "       kcbj.hdscbj \"hdscbj\",kcbj.hdwj \"hdwj\",sjzdz.z \"cd.z\"," +
               "       sum(nvl(kh.sl,0)) \"kcsl\",sum(nvl(kh.kkdsl,0)) \"kkdsl\"," +
               "       kcbj.bj \"bj\",kcbj.scbj \"scbj\",kcbj.wj \"wj\",kc.bjgs_id as \"gsId\"  " +
               "       from t_biz_kc kc  " +
               "  left join t_sys_gg gg on gg.id = kc.gg_id " +
               "  left join t_biz_kh kh  on kh.jtwz_id = gg.id and kh.sbgs_id = kc.bjgs_id and nvl(kh.cd_sjzdz_id, 0) = nvl(kc.cd_sjzdz_id, 0) " +
               "  left join t_sys_sjzdz sjzdz on kc.cd_sjzdz_id = sjzdz.id\n" +
               "  left join t_biz_kcbj kcbj on kcbj.bjgs_id = kc.bjgs_id and kcbj.gg_id = gg.id  " +
               "                           and nvl(kc.cd_sjzdz_id, 0) = nvl(kcbj.cd_sjzdz_id, 0) " +
               "                           and kcbj.bjrq = :bjrq " +
               " where kc.deleted = 0 " + (nbgsId == null ? "" : " and kc.bjgs_id = :bjgsId ") +
               " group by gg.id,sjzdz.id,gg.name,gg.code,gg.cz,gg.ggxh,gg.dw,kcbj.hdbj,kcbj.hdscbj,kcbj.hdwj,sjzdz.z,kcbj.bj,kcbj.scbj,kcbj.wj,kc.bjgs_id " +
               " order by gg.code";
        SQLQuery sqlQuery = getSession().createSQLQuery(query);
        if(nbgsId!=null){
            sqlQuery.setLong("bjgsId",nbgsId);
        }
        sqlQuery.setDate("bjrq",bjrq);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }

    public List<Map> findKcgl(Long bjgsId){
        String query = " select kc.id \"id\", gg.id \"gg.id\",gg.name \"gg.name\",gg.code \"gg.code\",gg.cz \"gg.cz\", " +
                "       gg.ggxh \"gg.ggxh\",gg.dw \"gg.dw\",cd.id \"cd.id\",cd.z \"cd.z\",kc.fromht \"fromht\"  " +
                "       from t_biz_kc kc left join t_sys_gg gg on gg.id = kc.gg_id  " +
                "       left join t_sys_sjzdz cd on cd.id = kc.cd_sjzdz_id  " +
                "       where kc.deleted = 0 and kc.bjgs_id = :bjgsId";
        SQLQuery sqlQuery = getSession().createSQLQuery(query);
        sqlQuery.setLong("bjgsId",bjgsId);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }
}
